package com.un.ebs.businessObject.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.un.ebs.core.enums.ColumnRefTypeEnum;
import com.un.ebs.core.enums.DataTypeEnum;
import com.un.ebs.core.enums.TableTypeEnum;
import com.un.ebs.core.exception.BusinessException;
import com.un.ebs.core.util.AESTool;
import com.un.ebs.core.util.StringUtils;
import com.un.ebs.sys.domain.*;
import com.un.ebs.sys.dto.ColumnDto;
import com.un.ebs.sys.dto.TableDto;
import com.un.ebs.sys.mapper.DataColumnMapper;
import com.un.ebs.sys.mapper.DataSourceMapper;
import com.un.ebs.sys.mapper.DataTableMapper;
import com.un.ebs.sys.mapper.SysBusinessObjectMapper;
import com.un.ebs.businessObject.service.DataFieldService;
import com.un.ebs.businessObject.service.DataTableService;
import com.un.ebs.sys.uitl.PoolUtils;
import com.un.ebs.sys.uitl.SqlCodeUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


/**
 * @Author aikun
 * 服务实现类
 * @ClassName DataTableServiceImpl
 * @Description DataTableServiceImpl
 * @Date 2020/1/8
 */
@Service
public class DataTableServiceImpl extends ServiceImpl<DataTableMapper, Table> implements DataTableService {
    @Autowired
    private DataTableMapper dataTableMapper;

    @Autowired
    private SysBusinessObjectMapper sysBusinessObjectMapper;

    @Autowired
    private DataSourceMapper dataSourceMapper;
    @Autowired
    private DataFieldService dataFieldService;
    @Autowired
    private DataColumnMapper dataColumnMapper;

    @Override
    public IPage<TableDto> list(Integer tableType, String tableName, String dataSourceId, int pageSize, int pageIndex) {
        Page<Table> page = new Page<>(pageIndex, pageSize);
        LambdaQueryWrapper<Table> lq = Wrappers.lambdaQuery();
        if (!StringUtils.isNullOrEmpty(tableName)) {
            lq.like(Table::getTableName, tableName);
        }
        if (!StringUtils.isNullOrEmpty(dataSourceId)) {
            lq.eq(Table::getDataSourceId, dataSourceId);
        }
        if (null != tableType) {
            lq.eq(Table::getTableType, tableType);
        }
        lq.orderByAsc(Table::getTableName);

        IPage<Table> pageList = dataTableMapper.selectPage(page, lq);
        List<Table> tables = pageList.getRecords();
        List<TableDto> tableDtos = new ArrayList<>();
        for (Table table : tables) {
            TableDto tableDto = new TableDto();
            BeanUtils.copyProperties(table, tableDto);
            if (null != table.getTableType()) {
                tableDto.setTableTypeIntVal(table.getTableType().getCode());
            }
            tableDtos.add(tableDto);
        }
        IPage<TableDto> tableDtoIPage = new Page<>(pageIndex, pageSize);
        tableDtoIPage.setRecords(tableDtos);
        return tableDtoIPage;
    }

    @Override
    @Transactional
    public void add(Table table) {
        Table table1 = toRepeat(table);
        if (table1 != null) {
            throw new BusinessException("表名称重复");
        }
        dataTableMapper.insert(table);
        addColumn(table);
    }

    @Override
    @Transactional
    public void update(Table table) {
        Table table1 = toRepeat(table);
        if (null != table1 && !table.getId().equals(table1.getId())) {
            throw new BusinessException("表名称重复");
        }
        if (!table.getIsRelease()) {
            LambdaQueryWrapper<Column> lq1 = Wrappers.lambdaQuery();
            lq1.eq(Column::getTableId, table.getId());
            dataColumnMapper.delete(lq1);
            addColumn(table);
        }
        dataTableMapper.updateById(table);
    }

    @Override
    public TableDto getById(String id) {
        Table table = dataTableMapper.selectById(id);
        TableDto tableDto = new TableDto();
        BeanUtils.copyProperties(table, tableDto);
        if (null != table.getTableType()) {
            tableDto.setTableTypeIntVal(table.getTableType().getCode());
        }
        return tableDto;
    }

    @Override
    @Transactional
    public void delete(String id) {
        Table table = dataTableMapper.selectById(id);
        if (table == null) {
            throw new BusinessException("表不存在");
        }
        //删除表里所有字段
        LambdaQueryWrapper<Column> lq = Wrappers.lambdaQuery();
        lq.eq(Column::getTableId, table.getId());
        dataColumnMapper.delete(lq);
        //删除表
        dataTableMapper.deleteById(id);
        DataSource dataSource = dataSourceMapper.selectById(table.getDataSourceId());
        if (dataSource == null) {
            throw new BusinessException("数据源不存在,创建表失败");
        }
        boolean lean = dataFieldService.isExistTable(table.getTableName(), dataSource.getName());
        if (lean) {
            //删除表
            dataFieldService.dropTable(table.getTableName(), dataSource.getName());
        }
    }

    @Override
    @Transactional
    public void batchDel(String[] ids) {
        if (null != ids && ids.length > 0) {
            for (String id : ids) {
                dataTableMapper.deleteById(id);
            }
        } else {
            throw new BusinessException("请至少选中一条数据");
        }

    }

    @Override
    @Transactional
    public void createTable(String id) {
        Table table = dataTableMapper.selectById(id);
        if (table == null) {
            throw new BusinessException("表不存在");
        }
        List<Field> fields = new ArrayList<>();
        LambdaQueryWrapper<Column> lq = Wrappers.lambdaQuery();
        lq.eq(Column::getTableId, table.getId());
        lq.orderByAsc(Column::getSort);
        List<Column> columns = dataColumnMapper.selectList(lq);
        for (Column column : columns) {
            Field field = new Field();
            field.setFiledName(column.getColumnName());
            field.setFiledLength(column.getLength());
            field.setFiledType(column.getColumnType());
            if (column.getColumnType().getCode() == ColumnRefTypeEnum.FK.getValue()) {
                field.setFkFieldName(column.getRefColumnName());
                field.setFkTableName(column.getRefTableName());
            }
            field.setNull(column.getIsNullable());
            field.setPk(column.getIsPk());
            field.setSort(column.getSort());
            fields.add(field);
        }
        DataSource dataSource = dataSourceMapper.selectById(table.getDataSourceId());
        dataFieldService.createTable(fields, table.getTableName(), dataSource.getName());
        //已经创建的表修改isRelease=true
        table.setIsRelease(true);
        dataTableMapper.updateById(table);
    }

    @Override
    @Transactional
    public void synchronizationTable(String id, String[] tableNameList) {
        DataSource ds = getDataSource(id);
        if (ds == null) {
            throw new BusinessException("数据源不存在");
        }
        List<TableDto> tables = getTables(ds, tableNameList);
        if (!StringUtils.isNullOrEmpty(tables)) {
            for (TableDto table : tables) {
                dataTableMapper.insert(table);
                if (!StringUtils.isNullOrEmpty(table.getColumnList())) {
                    for (Column column : table.getColumnList()) {
                        column.setTableId(table.getId());
                        dataColumnMapper.insert(column);
                    }
                }
            }
        }
    }

    @Override
    public List<String> queryTabList(String id) {
        DataSource ds = getDataSource(id);
        List<String> tableNames = new ArrayList<>();
        try {
            Connection conn = PoolUtils.getConnection(ds);
            //获取数据库的元数据
            DatabaseMetaData dbMetaData = conn.getMetaData();
            //catalog 其实也就是数据库名
            String catalog = conn.getCatalog();
            //从元数据中获取到所有的表名
            ResultSet rs = dbMetaData.getTables(catalog, null, null, new String[]{"TABLE"});
            //存放所有表名
            while (rs.next()) {
                tableNames.add(rs.getString("TABLE_NAME"));
            }
            //去掉重复的
            repeat(tableNames, ds);
        } catch (Exception e) {

        }
        return tableNames;
    }

    @Override
    @Transactional
    public void synchronizationColumn(String tableId) {
        Table table = dataTableMapper.selectById(tableId);
        DataSource ds = getDataSource(table.getDataSourceId());

        String[] tableNameList = new String[1];
        tableNameList[0] = table.getTableName();
        List<TableDto> tables = getTables(ds, tableNameList);
        if (!StringUtils.isNullOrEmpty(tables)) {
            List<Column> columnList = tables.get(0).getColumnList();
            LambdaQueryWrapper<Column> lq = Wrappers.lambdaQuery();
            lq.eq(Column::getTableId, table.getId());
            List<Column> columnList1 = dataColumnMapper.selectList(lq);
            if (!StringUtils.isNullOrEmpty(columnList1)) {
                List<Column> addList = new ArrayList<>();
                List<Column> delList = new ArrayList<>();
                addList.addAll(columnList);
                delList.addAll(columnList1);
                List<Column> upList = new ArrayList<>();
                for (Column column : columnList) {
                    for (Column column1 : columnList1) {
                        if (column1.getColumnName().equals(column.getColumnName())) {
                            upList.add(column1);
                            addList.remove(column);
                            delList.remove(column1);
                        }
                    }
                }
                if (!StringUtils.isNullOrEmpty(addList)) {
                    for (Column column : addList) {
                        column.setTableId(table.getId());
                        dataColumnMapper.insert(column);
                    }
                }
                if (!StringUtils.isNullOrEmpty(upList)) {
                    for (Column column : upList) {
                        dataColumnMapper.updateById(column);
                    }
                }
                if (!StringUtils.isNullOrEmpty(delList)) {
                    for (Column column : delList) {
                        dataColumnMapper.deleteById(column.getId());
                    }
                }
            }
        }
    }

    //表名称去重复
    public void repeat(List<String> tables, DataSource ds) {
        LambdaQueryWrapper<Table> lq = Wrappers.lambdaQuery();
        lq.eq(Table::getDataSourceId, ds.getId());
        List<Table> tableList = dataTableMapper.selectList(lq);
        if (!StringUtils.isNullOrEmpty(tableList)) {
            for (Table table : tableList) {
                tables.remove(table.getTableName());
            }
        }
    }

    public TableDto getTable(DataSource ds, String name) {
        try {
            Connection conn = PoolUtils.getConnection(ds);
            DatabaseMetaData metaData = conn.getMetaData();
            //ResultSet rsTable = metaData.getTables(conn.getCatalog(),conn.getSchema(),null,new String[]{"TABLE"});

            System.out.println("Table Name : " + name);
            ResultSet rs = metaData.getPrimaryKeys(conn.getCatalog(), null, name);
            while (rs.next()) {
                System.out.println("Primary Key : " + rs.getString("COLUMN_NAME"));
            }

            rs = metaData.getImportedKeys(conn.getCatalog(), null, name);
            while (rs.next()) {
                System.out.println("Exported Key : " + rs.getString("FKCOLUMN_NAME"));
            }
            rs = metaData.getColumns(conn.getCatalog(), null, name, null);
            while (rs.next()) {
                System.out.println(rs.getString("COLUMN_NAME"));
            }

        } catch (Exception e) {
            System.out.println(e.fillInStackTrace());
        }
        return null;
    }

    //获取所有表与字段
    public List<TableDto> getTables(DataSource ds, String[] tableNameList) {
        getTable(ds, tableNameList[0]);

        List<TableDto> tables = new ArrayList<>();
        try {
            Connection conn = PoolUtils.getConnection(ds);
            //获取数据库的元数据
            DatabaseMetaData dbMetaData = conn.getMetaData();
            //catalog 其实也就是数据库名
            String catalog = conn.getCatalog();
            List<String> tableNames = new ArrayList<>();
            if (null != tableNameList && tableNameList.length > 0) {
                tableNames.addAll(Arrays.asList(tableNameList));
            } else {
                //从元数据中获取到所有的表名
                ResultSet rs = dbMetaData.getTables(catalog, null, null, new String[]{"TABLE"});
                //存放所有表名
                while (rs.next()) {
                    tableNames.add(rs.getString("TABLE_NAME"));
                }
                repeat(tableNames, ds);
            }
            if (StringUtils.isNullOrEmpty(tableNames)) {
                throw new BusinessException("暂无需同步的表");
            }
            //查询每个表的字段
            for (String record : tableNames) {
                String sql = "select * from " + record;
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rstable = ps.executeQuery();
                //结果集元数据
                ResultSetMetaData meta = rstable.getMetaData();
                //表列数量
                int columeCount = meta.getColumnCount();
                List<Column> columnList = new ArrayList<>();
                ResultSet foreignKeyResultSet = dbMetaData.getImportedKeys(catalog, null, record);
                //根据表名获得主键结果集
                ResultSet pks = dbMetaData.getPrimaryKeys(catalog, null, record);
                String pkName = null;
                while (pks.next()) {
                    pkName = pks.getString("COLUMN_NAME");
                }
                TableDto table = new TableDto();
                table.setSupportLogicDelete(false);
                table.setSupportAudit(false);
                table.setSupportOperationLog(false);
                table.setSupportParent(false);
                table.setSupportTenant(false);
                table.setSupportSort(false);
                table.setSupportWorkflow(false);
                table.setIsRelease(true);
                table.setIsMany(false);
                Integer sortVal = 100;
                for (int i = 1; i <= columeCount; i++) {
                    ColumnDto column = new ColumnDto();
                    column.setColumnName(meta.getColumnName(i));
                    column.setLength(meta.getColumnDisplaySize(i));
                    switch (meta.getColumnTypeName(i)) {
                        case "DATETIME":
                            column.setColumnType(DataTypeEnum.DATETIME);
                            column.setLength(0);
                            break;
                        case "DATE":
                            column.setColumnType(DataTypeEnum.DATE);
                            break;
                        case "DECIMAL":
                            column.setColumnType(DataTypeEnum.NUMBER);
                            break;
                        case "INT":
                        case "TINYINT":
                        case "BIGINT":
                            column.setColumnType(DataTypeEnum.INT);
                            break;
                        case "BIT":
                            column.setColumnType(DataTypeEnum.BOOLEAN);
                            break;
                        default:
                            column.setColumnType(DataTypeEnum.STRING);
                            break;
                    }
                    column.setIsNullable(meta.isNullable(i) != 0);
                    column.setIsPk(false);
                    column.setSort(sortVal);
                    table.setTableType(TableTypeEnum.BusinessObject);
                    while (foreignKeyResultSet.next()) {
                        column.setRefColumnName(foreignKeyResultSet.getString("PKCOLUMN_NAME"));
                        column.setRefTableName(foreignKeyResultSet.getString("PKTABLE_NAME"));
                        column.setColumnType(DataTypeEnum.OBJECT);
                        column.setRefTypeIntVal(ColumnRefTypeEnum.FK.getCode());
                        table.setTableTypeIntVal(TableTypeEnum.BusinessObjectSet.getCode());
                    }
                    if (!StringUtils.isNullOrEmpty(pkName) && pkName.equals(meta.getColumnName(i))) {
                        column.setIsPk(true);
                        column.setColumnType(DataTypeEnum.STRING);
                        column.setLength(40);
                    }
                    columnList.add(column);
                    sortVal++;
                    switch (meta.getColumnName(i)) {
                        case "is_delete":
                            table.setSupportLogicDelete(true);
                            break;
                        case "tenant_id":
                            table.setSupportTenant(true);
                            break;
                        case "workflow_instance_id":
                            table.setSupportWorkflow(true);
                            break;
                        case "idx_num":
                            table.setSupportWorkflow(true);
                            break;
                        case "parent_id":
                            table.setSupportParent(true);
                            break;
                        case "submit_status":
                            table.setSupportAudit(true);
                            break;
                        case "audit_status":
                            table.setSupportAudit(true);
                            break;
                        default:
                            break;
                    }
                }
                table.setTableName(record);
                table.setDataSourceId(ds.getId());
                table.setColumnList(columnList);
                tables.add(table);
            }
        } catch (Exception e) {

        }
        return tables;
    }

    //获取数据源
    public DataSource getDataSource(String id) {
        LambdaQueryWrapper<DataSource> lq = Wrappers.lambdaQuery();
        lq.eq(DataSource::getId, id);
        DataSource ds = dataSourceMapper.selectOne(lq);
        ds.setIpAddr(AESTool.Decrypt(ds.getIpAddr()));
        ds.setPassword(AESTool.Decrypt(ds.getPassword()));
        return ds;
    }


    //判断表名是否重复
    public Table toRepeat(Table table) {
        LambdaQueryWrapper<Table> lq = Wrappers.lambdaQuery();
        lq.eq(Table::getTableName, table.getTableName());
        lq.eq(Table::getDataSourceId, table.getDataSourceId());
        Table table1 = dataTableMapper.selectOne(lq);
        return table1;
    }

    //新增表字段
    public void addColumn(Table table) {
        List<Field> fields = getFieldList(table);
        for (Field f : fields) {
            Column column = new Column();
            column.setColumnName(f.getFiledName());
            column.setLength(f.getFiledLength());
            column.setColumnType( f.getFiledType());
            column.setIsNullable(f.isNull());
            column.setIsPk(f.isPk());
            column.setTableId(table.getId());
            column.setSort(f.getSort());
            dataColumnMapper.insert(column);
        }
    }

    //获取新增字段
    public List<Field> getFieldList(Table table) {
//        List<Field> fields = new ArrayList<>();
//        Field field = new Field();
//        field.setPk(true);
//        field.setNull(false);
//        field.setFiledName("id");
//        field.setFiledLength(40);
//        field.setFiledType(1);
//        field.setSort(0);
//        fields.add(field);
        return SqlCodeUtil.getListByTable(table);
//        List<Field> fieldList = SqlCodeUtil.getListByTable(table);
//        fields.addAll(fieldList);
//        return fields;
    }

    @Override
    public IPage<SysBusinessObject> query(String dataSourceId, TableTypeEnum tableType, String tableName, int pageSize, int pageIndex) {
        Page<SysBusinessObject> page = new Page<>(pageIndex, pageSize);
        LambdaQueryWrapper<SysBusinessObject> lq = Wrappers.lambdaQuery();
        if (!StringUtils.isNullOrEmpty(dataSourceId)) {
            lq.eq(SysBusinessObject::getDataSourceId, dataSourceId);
        }
        if (!(tableType == null)) {
            lq.eq(SysBusinessObject::getTableType, tableType);
        }
        if (!StringUtils.isNullOrEmpty(tableName)) {
            lq.like(SysBusinessObject::getTableName, tableName);
        }
        return sysBusinessObjectMapper.selectPage(page, lq);

    }
}
